/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.querie.DBMS;

import com.querie.SI.PutItemData;
import com.querie.SI.PutSessionData;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import java.util.Map;
import java.lang.Integer;
import java.util.ArrayList;
import java.util.List;



/**
 *
 * author Naushin
 */
public class DBMSConnection {
	
public static void main(String[] args) {
    Connection conn = null;
    String url = "jdbc:mysql://localhost:3306/";
    String dbName = "querie";
    String driver = "com.mysql.jdbc.Driver";
    String userName = "root";
    String password = "root";
    String strSQL1 = "";
    String strSQL2 = "";


        ResultSet rs0 = null;
        ResultSet rs1 = null;
    	Statement stmt = null;
        String type="item";

    try{
      Class.forName(driver).newInstance();
      conn = DriverManager.getConnection(url+dbName,userName,password);
      System.out.println("Connected to the database");

           stmt = conn.createStatement();

           //strSQL1 = "Select sessionId, itemId, count(itemId) from querie.sessionqueries AS sq left outer join querie.qryitems AS qi on sq.queryId = qi.queryId group by sessionId, itemId";
            strSQL2 = "Select itemId, sessionId, count(sessionId) from querie.qryitems AS qi left outer join querie.sessionqueries AS sq on qi.queryId = sq.queryId where sq.sessionId NOT IN (66,99,396,450,923,1025,1150,1461,3922,6818,7087,7225,7347,7463,7938,8047,8307,8376,10593,19516,19644,19779,20001,21321,21345,21385,21468,21955,22011,22026,22088,22404,24947,25003,25543,25597,25830,25904,25972,26198,26377,26760,26950,27018,27226,29901,30121,30279,30300,30481,45269,53076,53093,53208,53260,53406,53436,53516,53738,53758,53926,53944,54217,60730,60957,61018,61081,61208,61407,61435,61455) group by itemId, sessionId";
           //strSQL2="Select sessionId, itemId, count(itemId) from querie.sessionqueries AS sq left outer join querie.qryitems AS qi on sq.queryId = qi.queryId where sq.sessionId= 30276 group by sessionId, itemId" ;
           //   strSQL2 = "Select itemId, sessionId, count(sessionId) from querie.qryitems AS qi left outer join querie.sessionqueries AS sq on qi.queryId = sq.queryId where qi.itemId = 8566 group by sessionId";
           // strSQL1="Select itemId from querie.sessionqueries AS sq left outer join querie.qryitems AS qi on sq.queryId=qi.queryId where sq.sessionId=45 group by sessionId, itemId";
           Map<String,String> map;
    if(type.equals("session"))
    {
            rs0 = stmt.executeQuery(strSQL1);

         
            
         //    Map<String,String> map;
            
           int thisSessionId=0;
           boolean sameSession;
            int sessionId=0;
    
           
          //Map<String,String> map;
          Map<String,String> map1;
            List<Integer> keys ;
          List<Integer> values ;

      List<Integer> sessionList=new ArrayList<Integer>();
           String item="", item1="";
            String count1="", count2="";
           int numberOfRecords=0;
       try{
          while(rs0.next() ){
                if(!rs0.isFirst()){
                   rs0.previous();
                   }
                sessionId = rs0.getInt(1);
                
               if (sessionId==thisSessionId)
               {
                   sameSession=true;
               }
              else
                       {

                   sameSession=false;
                                }
                if(sameSession){
                                         keys = new ArrayList<Integer>();
                                         values = new ArrayList<Integer>();
                                         keys.add(Integer.parseInt(item1));
                                         values.add(Integer.parseInt(count2));


                                   while(rs0.next()){
                                          if(rs0.getInt(1)==sessionId){
                                           item=Integer.toString(rs0.getInt(2));
                                           count1=Integer.toString(rs0.getInt(3));
                                           keys.add(Integer.parseInt(item));                                           values.add(Integer.parseInt(count1));
                                          }
                                         else
                                         
                                            break;
                                      } 

                                         PutSessionData pd1=new PutSessionData();
                                         numberOfRecords++;
                                         if(numberOfRecords % 10!=0)
                                         pd1.putAll(thisSessionId,keys,values);
                                         else
                                             System.out.println("Session id skipped"+thisSessionId);
                                             sessionList.add(thisSessionId);

                                            }
                        else {
                    item1=Integer.toString(rs0.getInt(2));
                  count2=Integer.toString(rs0.getInt(3));
                  thisSessionId=sessionId;
                             }

                    
               
           }

        }
       catch(Exception e){
       System.out.println(""+e);
       }
       

        }
 else {
            
              stmt=conn.createStatement();
              rs1=stmt.executeQuery(strSQL2);
              int thisItemId=0;
              int itemId=0;
              boolean sameItem;
              List<Integer> keys1 ;
              List<Integer> values1;

            String session="", session1="";
            String countitem1="", countitem2="";
   
          try{
          while(rs1.next()) {
                if(!rs1.isFirst()){
                rs1.previous();
                }
            	 itemId = rs1.getInt(1);

               if (itemId==thisItemId){
                          sameItem=true;
                                 }
                        else {
                      sameItem=false;
                      //break;
                        }

                 if(sameItem){
                              keys1 = new ArrayList<Integer>();
                              values1 = new ArrayList<Integer>();

                             keys1.add(Integer.parseInt(session1));
                             values1.add(Integer.parseInt(countitem2));
                       while(rs1.next())
                                       {
                                        if(itemId==rs1.getInt(1)){
                                       session=Integer.toString(rs1.getInt(2));
                                       countitem1=Integer.toString(rs1.getInt(3));
                                       keys1.add(Integer.parseInt(session));
                                       values1.add(Integer.parseInt(countitem1));
                                           }
                                       else break;
                                       }
                                        PutItemData pd1=new PutItemData();
                                        pd1.putAll(thisItemId,keys1,values1);
                                     }
                                    else {
                      session1=Integer.toString(rs1.getInt(2));
                      countitem2=Integer.toString(rs1.getInt(3));
                      thisItemId=itemId;
                                            }

                            

           }
        }

         catch(Exception s2)
          {
          System.out.println(s2);
          }
      //  Similarity1 sm=new Similarity1();
     //   sm.getSessions(sessionList);
        }
      conn.close();
      System.out.println("Disconnected from database");
    } catch (Exception e) {
      e.printStackTrace();
    }

    
    }
}
